package fy.input.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.LogFactory;

import fy.config.FyConfig;
import fy.util.DBUtil;

class MySQLSetDao {
	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(MySQLSetDao.class);

	public static List<MySQLSetBean> querySQLSet() {

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<MySQLSetBean> result = null;
		Statement st = null;
		try {
			conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());
			rs = conn.getMetaData().getTables(null, null,
					"mySqlSetMain".toUpperCase(), null);
			if (!rs.next()) {
				st = conn.createStatement();
				st.execute("create table mySqlSetMain(name varchar2(20) not null ,description varchar2(60) not null,database varchar2(2),errorMsg varchar2(100),nullMsg varchar2(100),fieldName varchar2(100),TS CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))");
				st.execute("create table mySqlSetSon(name varchar2(20) not null ,sql varchar2(1000) not null,TS CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))");
			}

			DBUtil.close(rs);

			pst = conn
					.prepareStatement("select name,description,database,errorMsg,nullMsg,fieldName from mySqlSetMain order by ts asc");

			rs = pst.executeQuery();
			result = new ArrayList<MySQLSetBean>();
			while (rs.next()) {
				result.add(new MySQLSetBean(rs.getString(1), rs.getString(2),
						rs.getString(4), rs.getString(5), rs.getString(3), rs
								.getString(6)));
			}
			DBUtil.close(rs, pst);
			pst = conn
					.prepareStatement("select name,sql from mySqlSetSon order by ts asc");

			rs = pst.executeQuery();

			while (rs.next()) {
				for (int i = 0, size = result.size(); i < size; i++) {
					if (rs.getString(1).equals(result.get(i).getName())) {
						result.get(i).addSql(rs.getString(2));
						break;
					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error("初始化ＳＱＬ设置发生异常", e);
		} finally {
			DBUtil.close(st);
			DBUtil.close(rs, pst, conn);
		}
		return result;
	}

	public static boolean changeSQLSet(String name, String description,
			String errorMsg, String nullMsg, String database, String fieldName,
			List<String> sqlList) {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());

			conn.setAutoCommit(false);
			pst = conn
					.prepareStatement("update mySqlSetMain set description=?,database=?,errormsg=?,nullmsg=?,fieldName=? where name=?");

			pst.setString(1, description);
			pst.setString(2, database);
			pst.setString(3, errorMsg);
			pst.setString(4, nullMsg);
			pst.setString(5, fieldName);
			pst.setString(6, name);

			pst.execute();

			DBUtil.close(pst);

			pst = conn.prepareStatement("delete from mySqlSetSon where name=?");

			pst.setString(1, name);

			pst.execute();

			pst = conn
					.prepareStatement("insert into mySqlSetSon (name,sql) values(?,?)");

			for (int i = 0, size = sqlList.size(); i < size; i++) {
				pst.setString(1, name);
				pst.setString(2, sqlList.get(i));
				pst.addBatch();
			}

			pst.executeBatch();
			conn.commit();
			success = true;
		} catch (Exception e) {
			DBUtil.rollback(conn);
			e.printStackTrace();
			log.error("新增ＳＱＬ设置发生异常", e);
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(pst, conn);
		}
		return success;
	}

	public static boolean addSQLSet(MySQLSetBean bean) {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());

			conn.setAutoCommit(false);
			pst = conn
					.prepareStatement("insert into mySqlSetMain (name, description, database,errormsg,nullmsg,fieldName) values(?,?,?,?,?,?)");

			pst.setString(1, bean.getName());
			pst.setString(2, bean.getDescription());
			pst.setString(3, bean.getDatabase());
			pst.setString(4, bean.getErrorMsg());
			pst.setString(5, bean.getNullMsg());
			pst.setString(6, bean.getFieldName());

			pst.execute();

			DBUtil.close(pst);

			pst = conn
					.prepareStatement("insert into mySqlSetSon (name,sql) values(?,?)");

			for (int i = 0, size = bean.getSqlList().size(); i < size; i++) {
				pst.setString(1, bean.getName());
				pst.setString(2, bean.getSqlList().get(i));
				pst.addBatch();
			}
			pst.executeBatch();
			conn.commit();
			success = true;
		} catch (Exception e) {
			DBUtil.rollback(conn);
			e.printStackTrace();
			log.error("新增ＳＱＬ设置发生异常", e);
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(pst, conn);
		}
		return success;
	}

	public static boolean delSQLSet(MySQLSetBean bean) {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());

			conn.setAutoCommit(false);
			pst = conn
					.prepareStatement("delete from mySqlSetMain where name = ?");

			pst.setString(1, bean.getName());

			pst.execute();

			DBUtil.close(pst);

			pst = conn
					.prepareStatement("delete from mySqlSetSon where name = ?");

			pst.setString(1, bean.getName());
			pst.execute();
			conn.commit();
			success = true;
		} catch (Exception e) {
			DBUtil.rollback(conn);
			e.printStackTrace();
			log.error("删除ＳＱＬ设置发生异常", e);
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(pst, conn);
		}
		return success;
	}
}
